package hbsi.edu.dao;

import com.mysql.jdbc.Connection;
import hbsi.edu.domain.Users;
import hbsi.edu.utils.DBUtil;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 程奕红
 * @version 1.0
 * 数据访问层的代码
 */
public class UsersDao {
    Connection connection;
    PreparedStatement statement;
    ResultSet res;
    //类中的方法是用来实现Users对Users表进行增删改查

    public int addUser(Users users){
        Users user=new Users();
        int n=0;
        //更新users表，参数对象user就是要更新的数据
        try {
            connection=DBUtil.getConnection();
            statement=connection.prepareStatement("insert into users(name,password,email,birthday)");
            statement.setString(1, users.getName());
            statement.setString(2, users.getPassword());
            statement.setString(3, users.getEmail());
            statement.setDate(4,new Date(user.getBirthday().getTime()));//java.util
            n=statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.release(statement,connection);
        }
        return n;
    }

    public int deleteUser(int id){

        int n=0;
        //更新users表，参数对象user就是要更新的数据
        try {
            connection=DBUtil.getConnection();
            statement=connection.prepareStatement("delete from users where id=?");
           statement.setInt(1,id);
           n=statement.executeUpdate();
//            n=statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.release(statement,connection);
        }
        return n;

    }

    public int updateUser(Users users){
        Users user=new Users();
        int n=0;
        //更新users表，参数对象user就是要更新的数据
        try {
            connection=DBUtil.getConnection();
            statement=connection.prepareStatement(" update users set name=?,password=?,email=?,birthday=? where id=?");
            statement.setString(1, users.getName());
            statement.setString(2, users.getPassword());
            statement.setString(3, users.getEmail());
            statement.setDate(4,new Date(user.getBirthday().getTime()));//java.util
            statement.setInt(5,user.getId());
            n=statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.release(statement,connection);
        }
        return n;
    }

    public Users getUserById(int id) throws SQLException {
        Users user=new Users();
        try {
            connection=DBUtil.getConnection();
            statement=connection.prepareStatement("select * from users where id =?");
            statement.setInt(1,id);
            res=statement.executeQuery();

            if(res.next()){

                user.setId(res.getInt(1));
                user.setName(res.getString(2));
                user.setPassword(res.getString(3));
                user.setEmail(res.getString(4));
                user.setBirthday(res.getDate(5));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.release(res,statement,connection);
        }
        return user;

    }
    public List<Users> getAllUsers() throws SQLException {


        List<Users> list=new ArrayList<>();
        try{
            connection= DBUtil.getConnection();
            statement=connection.prepareStatement("select * from users");
            res=statement.executeQuery();
            while(res.next()){
                Users user=new Users();//将每行读出的内容封装在一个对象中
                user.setId(res.getInt(1));
                user.setName(res.getString(2));
                user.setPassword(res.getString(3));
                user.setEmail(res.getString(4));
                user.setBirthday(res.getDate(5));
                list.add(user);
            }


            System.out.println("游标指针是否在最后一行的"+res.isAfterLast());

        }catch(SQLException  e){

        }  finally{
            DBUtil.release(res,statement,connection);
        }
            return list;
    }

    public void testScrollResultSet() throws SQLException {
        connection=DBUtil.getConnection();
        //再获取语句对象时，除了
        statement= connection.prepareStatement("select * from users",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
        res=statement.executeQuery();
        System.out.println(res.getString(2));
        res.next();
        System.out.println(res.getString(2));

    }
}
